﻿using System;
using System.Collections.Generic;
using System.Text;
using LabMS.BLL;
using LabMS.Model;
using Maticsoft.DBUtility;
using System.Data;
using System.Data.SqlClient;//请先添加引用

namespace ExtendBLL
{
    public class SuppliesRecipients : LabMS.BLL.SuppliesRecipients
    {
        public void ExtendAdd(LabMS.Model.SuppliesRecipients model, decimal iTotalNub)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into SuppliesRecipients(");
            strSql.Append("EncodedWarehouse,Numbers,Name,Words,Arrow,QuantityRecipients,Recipients,Laboratory,EncodedTime,UnitPrice,TotalAmount,Comment,Operator,OperatDate)");
            strSql.Append(" values (");
            strSql.Append("@EncodedWarehouse,@Numbers,@Name,@Words,@Arrow,@QuantityRecipients,@Recipients,@Laboratory,@EncodedTime,@UnitPrice,@TotalAmount,@Comment,@Operator,@OperatDate)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@EncodedWarehouse", SqlDbType.VarChar,50),
					new SqlParameter("@Numbers", SqlDbType.VarChar,100),
					new SqlParameter("@Name", SqlDbType.VarChar,50),
					new SqlParameter("@Words", SqlDbType.VarChar,20),
					new SqlParameter("@Arrow", SqlDbType.VarChar,50),
					new SqlParameter("@QuantityRecipients", SqlDbType.VarChar,20),
					new SqlParameter("@Recipients", SqlDbType.VarChar,30),
					new SqlParameter("@Laboratory", SqlDbType.VarChar,50),
					new SqlParameter("@EncodedTime", SqlDbType.DateTime),
					new SqlParameter("@UnitPrice", SqlDbType.VarChar,40),
					new SqlParameter("@TotalAmount", SqlDbType.VarChar,50),
					new SqlParameter("@Comment", SqlDbType.VarChar,100),
					new SqlParameter("@Operator", SqlDbType.VarChar,30),
					new SqlParameter("@OperatDate", SqlDbType.DateTime)};
            parameters[0].Value = model.EncodedWarehouse;
            parameters[1].Value = model.Numbers;
            parameters[2].Value = model.Name;
            parameters[3].Value = model.Words;
            parameters[4].Value = model.Arrow;
            parameters[5].Value = model.QuantityRecipients;
            parameters[6].Value = model.Recipients;
            parameters[7].Value = model.Laboratory;
            parameters[8].Value = model.EncodedTime;
            parameters[9].Value = model.UnitPrice;
            parameters[10].Value = model.TotalAmount;
            parameters[11].Value = model.Comment;
            parameters[12].Value = model.Operator;
            parameters[13].Value = model.OperatDate;

            CommandInfo comd1 = new CommandInfo();
            comd1.Parameters = parameters;
            comd1.CommandText = strSql.ToString();

            decimal iQuantityRecipients = 0;
            decimal.TryParse(model.QuantityRecipients, out iQuantityRecipients);
            
            
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("update InstrumentAndConsumables set ");
            strSql2.Append("Quantity=@Quantity");
            strSql2.Append(" where Numbers=@Numbers ");
            SqlParameter[] parameters2 =
            {
                new SqlParameter("@Quantity", SqlDbType.Int,4),
                new SqlParameter("@Numbers", SqlDbType.VarChar,30)
            };
            parameters2[0].Value = (iTotalNub - iQuantityRecipients);
            parameters2[1].Value = model.Numbers;

            CommandInfo comd2 = new CommandInfo();
            comd2.Parameters = parameters2;
            comd2.CommandText = strSql2.ToString();

            List<CommandInfo> listcomd = new List<CommandInfo>();
            listcomd.Add(comd1);
            listcomd.Add(comd2);

            try
            {
                DbHelperSQL.ExecuteSqlTran(listcomd);
            }
            catch
            {
                throw(new Exception("数据库执行错误!"));
            }
        }

        public List<LabMS.Model.SuppliesRecipients> ExtendGetModuleList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select s.[ID],EncodedWarehouse,Numbers,[Name],Words,Arrow,QuantityRecipients,"+
                                   "Recipients,Laboratory,EncodedTime,UnitPrice,TotalAmount,Comment,Operator,"+
                                   "OperatDate, Teacher_Name, l.Location_Name as lname1, l2.Lab_Name as lname2");
            strSql.Append(" FROM SuppliesRecipients s, Teacher t, Location l, Lab l2 ");
            strSql.Append(" where s.Recipients = t.Teacher_Code and l.ID = s.EncodedWarehouse and"+
                                   " l2.Lab_Code = s.Laboratory");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            DataSet ds = DbHelperSQL.Query(strSql.ToString());

            List<LabMS.Model.SuppliesRecipients> modelList = new List<LabMS.Model.SuppliesRecipients>();
            int rowsCount = ds.Tables[0].Rows.Count;
            if (rowsCount > 0)
            {
                LabMS.Model.SuppliesRecipients model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new LabMS.Model.SuppliesRecipients();
                    model.ID= long.Parse(ds.Tables[0].Rows[n]["ID"].ToString());
                    model.EncodedWarehouse = ds.Tables[0].Rows[n]["lname1"].ToString();
                    model.Numbers = ds.Tables[0].Rows[n]["Numbers"].ToString();
                    model.Name = ds.Tables[0].Rows[n]["Name"].ToString();
                    model.Words = ds.Tables[0].Rows[n]["Words"].ToString();
                    model.Arrow = ds.Tables[0].Rows[n]["Arrow"].ToString();
                    model.QuantityRecipients = ds.Tables[0].Rows[n]["QuantityRecipients"].ToString();
                    model.Recipients = ds.Tables[0].Rows[n]["Teacher_Name"].ToString();
                    model.Laboratory = ds.Tables[0].Rows[n]["lname2"].ToString();
                    if (ds.Tables[0].Rows[n]["EncodedTime"].ToString() != "")
                    {
                        model.EncodedTime = DateTime.Parse(ds.Tables[0].Rows[n]["EncodedTime"].ToString());
                    }
                    model.UnitPrice = ds.Tables[0].Rows[n]["UnitPrice"].ToString();
                    model.TotalAmount = ds.Tables[0].Rows[n]["TotalAmount"].ToString();
                    model.Comment = ds.Tables[0].Rows[n]["Comment"].ToString();
                    model.Operator = ds.Tables[0].Rows[n]["Operator"].ToString();
                    if (ds.Tables[0].Rows[n]["OperatDate"].ToString() != "")
                    {
                        model.OperatDate = DateTime.Parse(ds.Tables[0].Rows[n]["OperatDate"].ToString());
                    }
                    modelList.Add(model);
                }
            }
            return modelList;
        }
    }
}
